﻿using System;
using System.Collections.Generic;
using System.Web.Mvc;
using BLL;
using Model;
using System.Data.SqlClient;
using Common;
using System.Data;
using System.Web.UI.WebControls;
using System.Web;
using System.Text;
using System.Web.Services;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;

namespace WebPortal.Controllers
{
    public class SysTBViewController : ViewInfo
    {
        /// <summary>
        /// 视图列表
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        public ActionResult List(FormCollection collection, string dbName)
        {
            Dictionary<string, string> state = collection["state"].FromJSon<Dictionary<string, string>>();
            string expGroup = collection["ExpGroup"];
            if (state != null)
            {
                //取登陆信息，当前对接系统
                LoginInfo loginInfos = base.loginInfo();
                loginInfos.DBName = dbName;
                SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
                IDataParameter[] queryParas = new SqlParameter[state.Count + 4];
                int index = 0;
                foreach (string item in state.Keys)
                {
                    queryParas[index] = new SqlParameter("@" + item, state[item]);
                    index += 1;
                }
                queryParas[index] = new SqlParameter("@Rows", collection["rows"].ToString());
                queryParas[index + 1] = new SqlParameter("@PageNum", collection["page"].ToString());
                queryParas[index + 2] = new SqlParameter("@SysCode", Request.Cookies["MDM_TargetCode"].Value);
                queryParas[index + 3] = new SqlParameter("@ExpGroup", expGroup == null ? "" : expGroup);
                List<SysTBView> list = bll.GetList(queryParas);//视图列表信息
                int count = bll.GetListCount(queryParas);//记录数
                Dictionary<String, Object> jsonDict = new Dictionary<String, Object>();
                jsonDict.Add("total", count);
                jsonDict.Add("rows", list);
                return Json(jsonDict);
            }
            else
                return View();
        }
        /// <summary>
        /// 修改视图状态
        /// </summary>
        /// <param name="id">视图id</param>
        /// <param name="status">视图状态</param>
        public void UpStatus(string dbName,string id, int status)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            IDataParameter[] paras = new SqlParameter[8];
            paras[0] = new SqlParameter("@ID", id);
            paras[1] = new SqlParameter("@ViewName", "");
            paras[2] = new SqlParameter("@ViewType", "");
            paras[3] = new SqlParameter("@Definition", "");
            paras[4] = new SqlParameter("@Description", "");
            paras[5] = new SqlParameter("@Status", status);
            paras[6] = new SqlParameter("@JoinType", "");
            paras[7] = new SqlParameter("@UserName", loginInfos.Dbuser);
            bll.Update(paras, "Update");
        }
        /// <summary>
        /// 删除视图
        /// </summary>
        /// <param name="id">视图id</param>
        /// <returns></returns>
        public int DeleteItem(string dbName,string id)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            return bll.Delete(id);
        }
        /// <summary>
        /// 取视图推送名称
        /// </summary>
        /// <param name="id">视图id</param>
        /// <returns></returns>
        public ActionResult EditPushName(string dbName,string id)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            return Json(bll.EditPushName(id));
        }
        /// <summary>
        /// 保存视图推送名称
        /// </summary>
        /// <param name="id">视图id</param>
        /// <param name="pushName">推送名称</param>
        /// <returns></returns>
        public string SavePushName(string dbName,string id, string pushName, string pushSchema)
        {
            LoginInfo loginInfos = base.loginInfo();
            loginInfos.DBName = dbName;
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            return bll.SavePushName(id, pushName, pushSchema);
        }

        #region 失效功能方法
        [HttpPost]
        public ActionResult SaveItem(FormCollection collection)
        {
            Dictionary<string, string> query = collection["query"].FromJSon<Dictionary<string, string>>();
            Dictionary<string, string> selectView = collection["selectView"].FromJSon<Dictionary<string, string>>();
            Dictionary<string, string> selectArea = collection["selectArea"].FromJSon<Dictionary<string, string>>();
            Dictionary<string, string> onArea = collection["onArea"].FromJSon<Dictionary<string, string>>();
            Dictionary<string, string> whereArea = collection["whereArea"].FromJSon<Dictionary<string, string>>();
            Dictionary<string, string> orderByArea = collection["orderByArea"].FromJSon<Dictionary<string, string>>();
            try
            {
                LoginInfo loginInfos = base.loginInfo();
                SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
                string viewName = query["ViewName"];
                if (string.IsNullOrEmpty(viewName))
                    return Json("视图名称不能为空！");
                else
                {
                    List<Test_BaseView> list = bll.GetBaseViewList(loginInfos.ETLTargetCode);
                    if (list.FindIndex(o => { return o.EnName == loginInfos.ETLTargetCode + "." + viewName; }) > -1)
                        return Json("基础视图中存在先相同的视图名称，请使用其他视图名！");
                }

                IDataParameter[] queryParas = new SqlParameter[query.Count + 1];
                int index = 0;
                string newID = Guid.NewGuid().ToString();
                if (!string.IsNullOrEmpty(query["ID"]))
                    newID = query["ID"];
                foreach (string item in query.Keys)
                {
                    switch (item)
                    {
                        case "Status":
                            queryParas[index] = new SqlParameter("@" + item, int.Parse(query[item]));
                            break;
                        case "ID":
                            queryParas[index] = new SqlParameter("@" + item, newID);
                            break;
                        default:
                            queryParas[index] = new SqlParameter("@" + item, query[item]);
                            break;
                    }

                    index += 1;
                }
                queryParas[index] = new SqlParameter("@UserName", loginInfos.Dbuser);
                if (!bll.CheckViewData(query["Definition"].ToString()))
                {
                    return Json("定义的视图查询无数据，请检查视图定义。");
                }
                else
                {
                    bll.Update(queryParas, "Update");
                    foreach (string item in selectView.Keys)
                    {
                        if (!string.IsNullOrEmpty(selectView[item]))
                        {
                            IDataParameter[] paras = new SqlParameter[2];
                            paras[0] = new SqlParameter("@ViewID", newID);
                            paras[1] = new SqlParameter("@BaseViewID", selectView[item]);
                            bll.Update(paras, "Ship");
                        }
                    }
                    foreach (string item in selectArea.Keys)
                    {
                        if (item != "-1")
                        {
                            IDataParameter[] paras = new SqlParameter[3];
                            paras[0] = new SqlParameter("@ViewID", newID);
                            paras[1] = new SqlParameter("@BaseView", item.Replace("-", "."));
                            paras[2] = new SqlParameter("@ColumnName", selectArea[item]);
                            bll.Update(paras, "Area");
                        }
                    }
                    for (int i = 1; i <= onArea.Count / 2; i++)
                    {
                        IDataParameter[] paras = new SqlParameter[3];
                        paras[0] = new SqlParameter("@ViewID", newID);
                        paras[1] = new SqlParameter("@LeftColumn", onArea["LeftOn_" + i]);
                        paras[2] = new SqlParameter("@RightColumn", onArea["RightOn_" + i]);
                        bll.Update(paras, "On");
                    }
                    for (int i = 1; i <= whereArea.Count / 4; i++)
                    {
                        IDataParameter[] paras = new SqlParameter[5];
                        paras[0] = new SqlParameter("@ViewID", newID);
                        paras[1] = new SqlParameter("@ViewColumn", whereArea["ViewColumn_" + i]);
                        paras[2] = new SqlParameter("@Action", whereArea["Action" + i]);
                        paras[3] = new SqlParameter("@ActionText", whereArea["ActionText_" + i]);
                        paras[4] = new SqlParameter("@GroupBy", whereArea["GroupBy" + i]);
                        bll.Update(paras, "Where");
                    }
                    for (int i = 1; i <= orderByArea.Count / 3; i++)
                    {
                        IDataParameter[] paras = new SqlParameter[4];
                        paras[0] = new SqlParameter("@ViewID", newID);
                        paras[1] = new SqlParameter("@ViewColumn", orderByArea["Order_" + i]);
                        paras[2] = new SqlParameter("@SortType", orderByArea["SortType" + i]);
                        paras[3] = new SqlParameter("@SortNum", orderByArea["SortNum" + i]);
                        bll.Update(paras, "Order");
                    }
                }
                return Json("OK");
            }
            catch (Exception ex)
            {
                return Json(ex.Message);
            }
        }

        public string SelectView(string id)
        {
            LoginInfo loginInfos = base.loginInfo();
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            System.Text.StringBuilder html = new System.Text.StringBuilder();
            List<Test_BaseView> list = bll.GetBaseViewList(loginInfos.ETLTargetCode);
            if (list.Count > 0)
            {
                foreach (Test_BaseView model in list)
                {
                    html.Append(string.Format("<tr><td><input type=\"checkbox\" id=\"{0}\" value=\"{0}\" name=\"{1}\" class=\"{2}\" onclick=\"selectItem('{0}','{2}');\"/></td><td>&nbsp;{1}</td><td>{2}</td></tr>", model.ID, model.Name, model.EnName));
                }
            }
            return html.ToString();
        }

        public string GetTableInfo(string id, string viewID)
        {
            LoginInfo loginInfos = base.loginInfo();
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            SysPublicBLL pbll = new SysPublicBLL(loginInfos);
            System.Text.StringBuilder html = new System.Text.StringBuilder();
            List<SysNameValue> list = pbll.GetTableInfoList(id);
            DataSet ds_view = new DataSet();
            if (viewID.HasValue())
                ds_view = bll.SavedView(viewID, "1");
            string selected = string.Empty;
            if (ds_view != null && ds_view.Tables.Count > 0)
            {
                selected = ds_view.Tables[0].ToJson();
            }
            if (list.Count > 0)
            {
                foreach (SysNameValue model in list)
                {
                    string check = string.Empty;
                    string disable = string.Empty;
                    string enCode = model.Name.ToLower();
                    if (enCode == "mdp_guid" || enCode == "mdp_batchtime" || enCode == "mdp_isdelete")
                        disable = "disabled='disabled'";
                    if (viewID.HasValue())
                    {
                        if (selected.IndexOf(id + "." + model.Name) > -1)
                            check = " checked='checked'";
                    }
                    else
                        check = "checked='checked'";
                    html.Append(string.Format("<tr><td><input type=\"checkbox\" value=\"{2}.{0}\" id=\"{3}_{0}\" class=\"{0}\" onclick=\"selectColumn('{2}','{0}');\" {4}{5}/></td><td>{1}</td><td>{0}</td></tr>", model.Name, model.Value, id, id.Replace(".", "_"), check, disable));
                }
            }
            return html.ToString();
        }

        public string SavedView(string id, string index)
        {
            LoginInfo loginInfos = base.loginInfo();
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            DataSet ds = bll.SavedView(id, index);
            List<string> list = new List<string>();

            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0].ToJson();
            }
            return "";
        }

        public ActionResult ExportSql(FormCollection collection)
        {
            LoginInfo loginInfos = base.loginInfo();
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);
            string id = collection[0];
            int type = Convert.ToInt32(collection[1]);
            System.Text.StringBuilder tbScript = new System.Text.StringBuilder();
            DataTable dt = bll.GetItem(id, type);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    tbScript.Append(bll.GetCreateTbScript(dr[0].ToString(), loginInfos.ETLTargetType.ToString()));
                }
                tbScript.Append("\r\n");
            }
            System.IO.MemoryStream cvsOutPut = new System.IO.MemoryStream();
            byte[] data = Encoding.UTF8.GetBytes(tbScript.ToString());
            cvsOutPut.Write(data, 0, data.Length);
            return File(cvsOutPut.ToArray(), "text/plain", string.Format("CreateTableSql_{0}.sql", DateTime.Now.ToString("yyyyMMddHHmmss")));

        }

        public ActionResult ExportData(FormCollection collection)
        {
            LoginInfo loginInfos = base.loginInfo();
            SysTBViewBLL bll = new SysTBViewBLL(loginInfos);

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            bool isTxt = false;
            string[] ids = collection["IDs"].Split(',');
            string ExportType = collection["ExportType"];
            switch (ExportType)
            {
                case "CSV":
                    Response.AppendHeader("Content-Disposition", string.Format("attachment;filename=ExportData_{0}.csv", DateTime.Now.ToString("yyyyMMdd")));
                    Response.ContentType = "text/comma-separated-values";
                    isTxt = true;
                    break;
                case "TXT":
                    Response.AppendHeader("Content-Disposition", string.Format("attachment;filename=ExportData_{0}.txt", DateTime.Now.ToString("yyyyMMdd")));
                    Response.ContentType = "text/plain";
                    isTxt = true;
                    break;
            }
            foreach (string id in ids)
            {
                dt = bll.ExportData(id);
                ds.Tables.Add(dt);
            }
            if (isTxt)
            {
                System.Text.StringBuilder txtData = new System.Text.StringBuilder();
                foreach (DataRow dr in dt.Rows)
                {
                    List<string> dcData = new List<string>();
                    for (int dci = 0; dci < dt.Columns.Count; dci++)
                    {
                        dcData.Add(dr[dci].ToString());
                    }
                    txtData.Append(string.Join(",", dcData));
                    txtData.Append(Environment.NewLine);
                }
                this.HttpContext.Response.Write(txtData.ToString());
                this.HttpContext.Response.End();
            }
            else
            {
                System.IO.StreamReader sr = ToExcelSheet(ds);
                //System.IO.MemoryStream cvsOutPut = new System.IO.MemoryStream();
                //byte[] data = Encoding.UTF8.GetBytes("");
                //cvsOutPut.Write(data, 0, data.Length);
                return File(sr.BaseStream, "application/msexcel", string.Format("ExcelData_{0}.xls", DateTime.Now.ToString("yyyyMMdd")));


                //return File(st, "application/msexcel", string.Format("excel_{0}.sql", DateTime.Now.ToString("yyyyMMdd")));

                //                GridView gv = new GridView();
                //                gv.DataSource = dt;
                //                gv.DataBind();
                //                this.HttpContext.Response.Write(string.Format(@"
                //            <html><head><meta http-equiv=""Content-Type"" content=""text/html;charset=utf8""></head> 
                //            <body>{0}</body></html>", gv.ToRenderString()));
                //                this.HttpContext.Response.End();
            }
            return View();
        }
        #region 导出到excel多sheet
        private StreamReader ToExcelSheet(DataSet ds)
        {
            int testnum = ds.Tables.Count - 1;

            Excel.Application appExcel;
            appExcel = new Excel.Application();

            Excel.Workbook workbookData;
            Excel.Worksheet worksheetData;

            workbookData = appExcel.Workbooks.Add(Missing.Value);
            //
            //workbookData.Worksheets.Delete();
            for (int k = 0; k < ds.Tables.Count; k++)
            {
                worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                worksheetData.Name = "Sheet_" + testnum.ToString();
                testnum--;
                if (ds.Tables[k] != null)
                {
                    for (int j = 0; j < ds.Tables[k].Columns.Count; j++)
                    {
                        worksheetData.Cells[1, j + 1] = ds.Tables[k].Columns[j].ColumnName;
                    }
                    for (int i = 0; i < ds.Tables[k].Rows.Count; i++)
                    {
                        for (int j = 0; j < ds.Tables[k].Columns.Count; j++)
                        {
                            worksheetData.Cells[i + 2, j + 1] = ds.Tables[k].Rows[i][j].ToString();
                        }
                    }
                }

                worksheetData.Columns.EntireColumn.AutoFit();
                workbookData.Saved = true;

            }
            string sheetName = Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            string filePath = HttpContext.Server.MapPath("/ExportData/");
            string strFileName = filePath + sheetName;

            workbookData.SaveAs(strFileName);
            workbookData.Close();
            appExcel.Quit();
            System.IO.StreamReader sr = new StreamReader(strFileName);
            string[] files = System.IO.Directory.GetFiles(filePath);
            foreach (string s in files)
            {
                DateTime fileDate = System.IO.File.GetLastWriteTime(s);
                TimeSpan ts = DateTime.Now - fileDate;
                if (ts.Days > 0)
                    System.IO.File.Delete(s);
            }
            return sr;
        }

        #endregion
        #endregion
    }
}
